﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuantitativeIndicator.Tools
{
    class DataBase
    {
        public SqlConnection connection; // 数据库连接
        public SqlCommand command; //数据库连接命令
        //初始化DataBase即打开连接
        public DataBase()
        {
            this.connection = new SqlConnection("Server=HUZHUOWENNB; User=sa; PWD=111111;database=DFZQ;Asynchronous Processing=true");
            this.connection.Open();
            this.command = this.connection.CreateCommand();
        }
        //关闭数据库
        public void closeDataBase()
        {
            connection.Close();
        }
        //获取指数价格序列,排列按照日期倒序,其中noOfCalPoints可能序列达不到数量，如达不到
        public double[] getIndexSeries(int noOfCalPoints,string indexTradingCode,string calDate)
        {
            double[] indexPriceList;
            //首先需要搞清楚，从calDate往前推，是否有足够的noOfCalPoints个点
            int leftDatesNum = getIndexTradedDatesNum(indexTradingCode,"10000000",calDate);
 
            command.CommandText = "select top " +noOfCalPoints.ToString()+ " indexDaily.closePrice from dbo.indexDaily,indexList where indexDaily.indexCode=indexList.indexCode and indexList.tradingCode=" + indexTradingCode + " and indexDaily.tradingDate<=" + calDate + " order by indexDaily.tradingDate desc";
            SqlDataReader sdr = command.ExecuteReader();

            indexPriceList=new double[noOfCalPoints];
            int i = 0;

            while (sdr.Read())
            {
                indexPriceList[i] = Double.Parse(sdr["closePrice"].ToString());
                i++;
                //indexPriceList.Add(Double.Parse(sdr["closePrice"].ToString()));
            }
            sdr.Close();
            return indexPriceList;
        }
        //获取交易日列表,缺省获得所有交易日列表
        public List<string> getTradingDate(string startDate="10000000",string endDate="99999999")
        {
            command.CommandText = "select * from tradingDates where tradingDate between "+ startDate+" and "+endDate+ " order by tradingDate";
            SqlDataReader sdr = command.ExecuteReader();
            List<string> tradingDateList=new List<string>();
            //string[] tradingDateArr;
            while (sdr.Read())
            {
                tradingDateList.Add(sdr["tradingDate"].ToString());
            }
            sdr.Close();
            //tradingDateArr = tradingDateList.ToArray();
            return tradingDateList;
        }
        //获取指数收益率，rtnMtd=normal表示普通收益类型，rtnMtd=log表示log收益类型，排列按照日期倒序
        public double[] getIndexRtnSeries(int noOfCalPoints,string indexTradingCode, string calDate,string rtnMtd)
        {
            double[] tmpIndexSeries = getIndexSeries(noOfCalPoints+1,indexTradingCode,calDate);
            double[] tmpIndexRtnSeries;

            int N = tmpIndexSeries.Length;
            tmpIndexRtnSeries=new double[N-1];

            for (int i = 0; i < N-1; i++)
            {
                if (rtnMtd == "normal")
                {
                    tmpIndexRtnSeries[i] = tmpIndexSeries[i] / tmpIndexSeries[i + 1] - 1;
                }
                else if(rtnMtd=="log")
                {
                    tmpIndexRtnSeries[i]=Math.Log10(tmpIndexSeries[i]/tmpIndexSeries[i+1]);
                }
            
            }
                return tmpIndexRtnSeries;
 
        }
        //得到数据库中更新的最近交易日
        public string getLastIndexTradedDate(string indexTradingCode)
        {
            string returnDate;         
            command.CommandText = "select max(indexDaily.tradingDate) as lastDate from indexDaily,indexList where indexDaily.indexCode=indexList.indexCode and indexList.tradingCode=" + indexTradingCode;
            SqlDataReader sdr = command.ExecuteReader();
            sdr.Read();
            returnDate = sdr["lastDate"].ToString();
            sdr.Close();
            return returnDate;
        }
        //判断计算日期是否超出最近交易日期
        public Boolean isOutOfLastTradedDate(string indexTradingCode, string calDate)
        {
            string lastDate = getLastIndexTradedDate(indexTradingCode);
            if (int.Parse(calDate) > int.Parse(lastDate))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //判断计算日期是否超出开始交易日startDate,貌似没有多大用处，主要是用getIndexStartDate
        public Boolean isOutOfStartDate(string indexTradingCode, string calDate)
        {
            string tmpDate = getIndexStartDate(indexTradingCode);
            if (int.Parse(calDate) <int.Parse(tmpDate))
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        //得到指数的编制开始日期startDate
        public string getIndexStartDate(string indexTradingCode)
        {
            string tmpDate;
            command.CommandText = "select startDate from indexList where tradingCode=" + indexTradingCode;
            SqlDataReader sdr=command.ExecuteReader();

            sdr.Read();
            tmpDate = sdr["startDate"].ToString();
            sdr.Close();

            return tmpDate;
        }
        public int getIndexTradedDatesNum(string indexTradingCode, string startDate="10000000",string endDate="99999999")
        {
            string tmpDate = getIndexStartDate(indexTradingCode);
            if (int.Parse(startDate) < int.Parse(tmpDate))
            {
                startDate = tmpDate;
            }
            int tmpNum;
            command.CommandText = "select count(indexDaily.tradingDate) as tradedDateNum from indexDaily,indexList where indexDaily.indexCode=indexList.indexCode and indexList.tradingCode="+indexTradingCode+" and indexDaily.tradingDate between "+ startDate +" and "+endDate;
            SqlDataReader sdr = command.ExecuteReader();
            sdr.Read();
            tmpNum = int.Parse(sdr["tradedDateNum"].ToString());
            sdr.Close();
            return tmpNum;
        }
    }
}
